package com.zut.dao.impl;

import com.zut.dao.StudentDao;
import com.zut.entity.Student;
import com.zut.util.JdbcUtil;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SingleColumnRowMapper;

import java.util.List;
import java.util.Map;

/**
 * 学生持久层实现类
 */
public class StudentDaoImpl implements StudentDao {

    private JdbcTemplate template = JdbcUtil.template;

    /**
     * 查询最大学号
     * @param classId
     * @return
     */
    @Override
    public String findMaxId(Integer classId) {
        try {
            return template.queryForObject("select max(id) from student where classid=?",new SingleColumnRowMapper<>(String.class),classId);
        }catch (EmptyResultDataAccessException e){
            return null;
        }
    }

    /**
     * 添加
     * @param stu
     */
    @Override
    public void addStudent(Student stu) {
        String sql = "insert into student values(?,?,?,?,?,?,?,?)";
        template.update(sql,stu.getId(),stu.getName(),stu.getGender(),stu.getPhoneNum(),stu.getAddress(),stu.getPassword(),stu.getEnrollmentTime(),stu.getClassId());
    }


    /**
     * 查询所有学生
     * @return
     */
    @Override
    public List<Map<String, Object>> findAllStudents() {

        String sql = "select student.id,student.name,student.gender,classes.name className from student left join classes on student.classid=classes.id";

        return template.queryForList(sql);
    }


    /**
     * 根据姓名模糊查询
     * @param s
     * @return
     */
    @Override
    public List<Map<String, Object>> findStudentsLikeName(String s) {
        String sql =  "select student.id,student.name,student.gender,classes.name className from student left join classes on student.classid=classes.id where student.name like ?";
        return template.queryForList(sql,s);
    }

    /**
     * 根据班级名称模糊查询
     * @param s
     * @return
     */
    @Override
    public List<Map<String, Object>> findStudentsLikeClassName(String s) {
        String sql =  "select student.id,student.name,student.gender,classes.name className from student left join classes on student.classid=classes.id where classes.name like ?";
        return template.queryForList(sql,s);
    }

    /**
     * 修改学生班级
     * @param stuId
     * @param clsId
     */
    @Override
    public void updateClassesById(String stuId, String clsId) {

        String sql = "update student set classId=? where id=?";

        template.update(sql,clsId,stuId);


    }

    /**
     * 根据id删除学生
     * @param stuId
     * @return
     */
    @Override
    public int deleteStudentById(String stuId) {
        return template.update("delete from student where id=?",stuId);
    }

    /**
     * 查询需要添加成绩的学生信息
     * @param tid
     * @param id
     * @return
     */
    @Override
    public List<Student> findStudentsByTidAndCid(String tid, int id) {
        String sql = "select * from student where classId in (select classId from teachingclass where tid=?) and id not in (select sid from score where cid =?)";
        return template.query(sql,new BeanPropertyRowMapper<>(Student.class),tid,id);
    }

    /**
     * 根据学号和密码查询
     * @param sid
     * @param password
     * @return
     */
    @Override
    public Student findStudentByIdAndPassword(String sid, String password) {
        try {
            return  template.queryForObject("select * from student where id=? and password=?",new BeanPropertyRowMapper<>(Student.class),sid,password);
        }catch (EmptyResultDataAccessException e){
            return null;
        }
    }

    /**
     * 学生表密码修改
     * @param id
     * @param newPwd
     */
    @Override
    public void updatePassword(String id, String newPwd) {
        template.update("update student set password=? where id=?",newPwd,id);

    }

    /**
     * 修改手机号
     * @param id
     * @param newPhoneNum
     */
    @Override
    public void updatePhoneNum(String id, String newPhoneNum) {

        template.update("update student set phoneNum=? where id=?",newPhoneNum,id);

    }
    public void updateAddress(String id, String newAddress) {

        template.update("update student set address=? where id=?",newAddress,id);
    }
}
